package com.javen.bankpay;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class PayInterfaceService {
	
	public void saveDebitCard(Record record){
		Db.save("debit_card", record);
	}
	
	public void updateDebitCard(Record record){
		Db.update("debit_card", record);
	}

	public List<Record> getCards(String table, String openID, String card_no){
		List<Record> list =new ArrayList<Record>();
		String sql = "";
		if(card_no.equals("")){
			sql = "select a.*, b.card_type, b.bank_name from "+table+" a left join card_type b on left(a.card_no,6) = b.card_bin where a.openID = '"+openID+"' ";
		}else if(card_no.equals("getReduce")){
			sql = "select a.*, b.parOpenId from "+table+" a left join users b on a.openID = b.openID where a.reduce_fee = '1' and b.openId <> b.parOpenId ";
		}else{
			sql = "select * from "+table+" a where a.openID = '"+openID+"' and a.card_no = '"+card_no+"' ";
		}
		if(Db.find(sql) == null){
			return list;
		}else{
			return Db.find(sql);
		}
	}
	
	public List<Record> getDebits(String openID){
		String sql = "select * from debit_card a where a.openID = '"+openID+"' ";
		return Db.find(sql);
	}
	
	public List<Record> getAllDebits(){
		String sql = "select * from debit_card order by id asc";
		return Db.find(sql);
	}
	
	public int getTotalFee(String openID){//条件：1、支付成功 2、60天之内
		String sql = "select ifnull(round(sum(totalFee)/100),0) as totalFee from pay_order a where a.openID = '"+openID+"' and "
							+ "a.state = '5' and a.settleStatus = '0' and DATE_SUB(CURDATE(), INTERVAL 60 DAY) <= date(a.createTime) ";
		return Db.findFirst(sql).getLong("totalFee").intValue();
	}
	
	public void addCredit(Record record){
		Db.save("credit_card", record);
	}
	
	public void deleteCredit(Record record){
		Db.delete("credit_card", record);
	}
	
	public void addOrder(Record record){
		Db.save("pay_order", record);
	}
	
	public List<Record> getOrder(String content, String type, String start, String limit){
		String sql = "";
		if(type.equals("one")){
			sql = "select * from pay_order a where a.agentOrderNo = '"+content+"' ";
		}else{
			sql = "select a.id, right(credit_card_no,4) as credit_card_no, round((totalFee/100),2) as totalFee, "
				+ "(fee0/1000) as fee0, (totalFee*(1000 - fee0)/100000) as getFee, updateTime "
				+ " from pay_order a where a.openID = '"+content+"' and a.state = '5' order by id limit "+start+","+limit+" ";
		}
		return Db.find(sql);
	}
	
	public int getOrderCount(String openID){
		String sql = "select count(*) as count from pay_order a where a.openID = '"+openID+"' and a.state = '5' ";
		return Db.findFirst(sql).getLong("count").intValue();
	}
	
	public int getAccount(String openID){
		String sql = "select count(*) as count from users a where a.parOpenId = '"+openID+"' and a.openId <> a.parOpenId ";
		return Db.findFirst(sql).getLong("count").intValue();
	}
	
	public List<Record> getAccountList(String openID, String start, String limit){
		String sql = "select ifnull(round(sum(d.totalFee)/100,2),0) as totalFee, b.nickName from"
				+ " (select * from users a where a.parOpenId = '"+openID+"' and a.openId <> a.parOpenId) b left join "
				+ "(select * from pay_order c where c.state = '5' and c.settleStatus = '0') "
				+ "d on b.openId = d.openID group by b.openId order by b.id limit "+start+", "+limit+" ";
		return Db.find(sql);
	}
	
	public void updateOrder(Record record){
		Db.update("pay_order", record);
	}
}
